﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WaveMaster.Holidays.Club.Gateway;
using WaveMaster.Holidays.Club.Domain;
using System.Collections;
using MySql.Data.MySqlClient;
using WaveMaster.Holidays.Club.SQL;
using System.IO;

namespace WaveMaster.Holidays.Club.Service
{
    public class PassportService : IService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        public PassportService(DataBaseConnectivity dataBaseConectivity)
        {
            m_dataBaseConectivity = dataBaseConectivity;
        }
        public PassportService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
        }
        public IList LoadAllPassport()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassportSQL.GetAllPassport(), m_dataBaseConectivity.InterfaceSqlConnection);
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    Passport user = new Passport(rd);
                    result.Add(user);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public Passport GetPassportByPassportNo(string passportNo)
        {
            Passport user = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassportSQL.GetPassportByPassportNo(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passportNo", MySqlDbType.VarChar).Value = passportNo;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    user = new Passport(rd);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return user;
        }

        public Passport GetPassportByID(long id)
        {
            Passport result = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassportSQL.GetPassportByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passportID", MySqlDbType.Int32).Value = id;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result = new Passport(rd);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public IList SearchPassportByCodeName(string p)
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassportSQL.SearchPassportByCodeName(p), m_dataBaseConectivity.InterfaceSqlConnection);
                //cmd.Parameters.Add("code", MySqlDbType.VarChar).Value = p;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    result.Add(new Passport(rd));
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        public void DeletePassport(Passport user)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassportSQL.DeletePassportByID(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passportID", MySqlDbType.Int32).Value = user.m_passportID;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public void UpdatePassport(Passport m_user)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassportSQL.UpdatePassport(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passportNo", MySqlDbType.VarChar).Value = m_user.m_passportNo;
                cmd.Parameters.Add("m_issueDate", MySqlDbType.Datetime).Value = m_user.m_issueDate;
                cmd.Parameters.Add("m_expireDate", MySqlDbType.Datetime).Value = m_user.m_expireDate;
                cmd.Parameters.Add("m_country", MySqlDbType.VarChar).Value = m_user.m_country;
                cmd.Parameters.Add("m_officeIssue", MySqlDbType.VarChar).Value = m_user.m_officeIssue;
                cmd.Parameters.Add("m_name", MySqlDbType.VarChar).Value = m_user.m_name;
                cmd.Parameters.Add("m_gender", MySqlDbType.VarChar).Value = m_user.m_gender;
                cmd.Parameters.Add("m_birthPlace", MySqlDbType.VarChar).Value = m_user.m_birthPlace;
                cmd.Parameters.Add("m_birthDate", MySqlDbType.Datetime).Value = m_user.m_birthDate;
                cmd.Parameters.Add("m_nationality", MySqlDbType.VarChar).Value = m_user.m_nationality;
                cmd.Parameters.Add("m_placeOfResidence", MySqlDbType.VarChar).Value = m_user.m_placeOfResidence;
                cmd.Parameters.Add("m_occupation", MySqlDbType.VarChar).Value = m_user.m_occupation;
                cmd.Parameters.Add("m_maritalStatus", MySqlDbType.VarChar).Value = m_user.m_maritalStatus;
                cmd.Parameters.Add("m_address", MySqlDbType.VarChar).Value = m_user.m_address;
                cmd.Parameters.Add("m_city", MySqlDbType.VarChar).Value = m_user.m_city;
                cmd.Parameters.Add("m_zipcode", MySqlDbType.VarChar).Value = m_user.m_zipcode;
                cmd.Parameters.Add("m_remark", MySqlDbType.VarChar).Value = m_user.m_remark;
                cmd.Parameters.Add("m_idnumber", MySqlDbType.VarChar).Value = m_user.m_idnumber;
                cmd.Parameters.Add("m_modfctnDate", MySqlDbType.Datetime).Value = DateTime.Now; //m_user.UserName;
                cmd.Parameters.Add("m_modfctnBy", MySqlDbType.Int32).Value = m_user.m_modfctnBy;
                cmd.Parameters.Add("m_passportID", MySqlDbType.Int32).Value = m_user.m_passportID;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            } 
        }

        public void InserPassport(Passport m_user)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassportSQL.InsertPassport(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_passportNo", MySqlDbType.VarChar).Value = m_user.m_passportNo;
                cmd.Parameters.Add("m_issueDate", MySqlDbType.Datetime).Value = m_user.m_issueDate;
                cmd.Parameters.Add("m_expireDate", MySqlDbType.Datetime).Value = m_user.m_expireDate;
                cmd.Parameters.Add("m_country", MySqlDbType.VarChar).Value = m_user.m_country;
                cmd.Parameters.Add("m_officeIssue", MySqlDbType.VarChar).Value = m_user.m_officeIssue;
                cmd.Parameters.Add("m_name", MySqlDbType.VarChar).Value = m_user.m_name;
                cmd.Parameters.Add("m_gender", MySqlDbType.VarChar).Value = m_user.m_gender;
                cmd.Parameters.Add("m_birthPlace", MySqlDbType.VarChar).Value = m_user.m_birthPlace;
                cmd.Parameters.Add("m_birthDate", MySqlDbType.Datetime).Value = m_user.m_birthDate;
                cmd.Parameters.Add("m_nationality", MySqlDbType.VarChar).Value = m_user.m_nationality;
                cmd.Parameters.Add("m_placeOfResidence", MySqlDbType.VarChar).Value = m_user.m_placeOfResidence;
                cmd.Parameters.Add("m_occupation", MySqlDbType.VarChar).Value = m_user.m_occupation;
                cmd.Parameters.Add("m_maritalStatus", MySqlDbType.VarChar).Value = m_user.m_maritalStatus;
                cmd.Parameters.Add("m_address", MySqlDbType.VarChar).Value = m_user.m_address;
                cmd.Parameters.Add("m_city", MySqlDbType.VarChar).Value = m_user.m_city;
                cmd.Parameters.Add("m_zipcode", MySqlDbType.VarChar).Value = m_user.m_zipcode;
                cmd.Parameters.Add("m_remark", MySqlDbType.VarChar).Value = m_user.m_remark;
                cmd.Parameters.Add("m_idnumber", MySqlDbType.VarChar).Value = m_user.m_idnumber;
                cmd.Parameters.Add("m_cretnDate", MySqlDbType.Datetime).Value = DateTime.Now;//m_user.CreatenDate;
                cmd.Parameters.Add("m_cretnBy", MySqlDbType.Int32).Value = m_user.m_cretnBy;
                cmd.Parameters.Add("m_modfctnDate", MySqlDbType.Datetime).Value = DateTime.Now; //m_user.UserName;
                cmd.Parameters.Add("m_modfctnBy", MySqlDbType.Int32).Value = m_user.m_cretnBy;
                //LogService.WriteLogExecuteSQLCommand(cmd);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.CommandText = PassportSQL.GetMaxPassportID();
                m_user.m_passportID = Convert.ToInt64(cmd.ExecuteScalar());
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }


        public IList InsertCountry()
        {
            IList result = new ArrayList();
            try
            {
                
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassportSQL.InsertCountry(), m_dataBaseConectivity.InterfaceSqlConnection);
                //LogService.WriteLogExecuteSQLCommand(cmd);
                StreamReader rd = new StreamReader(@"F:\WavemasterHolidaysClub\countries.txt");
                string cc;
                while (!rd.EndOfStream)
                {
                    try
                    {
                        cc = rd.ReadLine();
                        string[] ctr = cc.Split(';');
                        cmd.Parameters.Clear();
                        cmd.Parameters.Add("m_countryCode", MySqlDbType.VarChar).Value = ctr[1];
                        cmd.Parameters.Add("m_countryName", MySqlDbType.VarChar).Value = ctr[0];
                        cmd.ExecuteNonQuery();
                    }
                    catch { }
                }
               
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
        public IList LoadAllCountry()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                MySqlCommand cmd = new MySqlCommand(PassportSQL.GetAllCountry(), m_dataBaseConectivity.InterfaceSqlConnection);
                //LogService.WriteLogExecuteSQLCommand(cmd);
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    Country user = new Country(rd);
                    result.Add(user);
                }
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }
    }
}
